#!/usr/bin/python2.4
#
#
#  Licensed under the Apache License, Version 2.0 (the "License");
#  you may not use this file except in compliance with the License.
#  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing, software
#  distributed under the License is distributed on an "AS IS" BASIS,
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#  See the License for the specific language governing permissions and
#  limitations under the License.

from sqlalchemy import *
from xml.dom.minidom import parse, parseString

class SubmitToDb:
    """ This class will process a submission and save it to a database
    """
    
    def __init__(self, db_url='sqlite:///odk.db', opts={'flatten':True}):
        """No matter what database you're using, the function takes a single parameter that's a URI, of the
        form "engine://user:password@host:port/database". Most of these options can be omitted; for example,
        if you're connecting to a PostgreSQL database on the default PostgreSQL port (port 5432), the URI
        would be something like "postgres://scott:tiger@localhost/demodb". SQLite lets you omit everything
        but the filename, so opening a file named tutorial.db in the current directory becomes
        "sqlite:///tutorial.db". Or, to open the file "/tmp/tutorial/joindemo.db", the URI becomes
        "sqlite:////tmp/tutorial/joindemo.db". (Yes, that's four slashes in a row. Two before the (empty)
        hostname section, then one before the database section, and one final slash at the start of the path
        "/tmp/tutorial/joindemo.db".)
        """
        global db
        global submissions
        global flatten
        
        db = create_engine(db_url)
        flatten = opts['flatten']
        
        # create the tables if they don't exist
        metadata = MetaData(db)
        
        try:
            users = Table('users', metadata, autoload=True)
        except:
            users = Table('users', metadata,
                Column('user_id', Integer, primary_key=True),
                Column('username', String(40)),
                Column('password', String(255)),
                Column('is_data_collector', Boolean),
                Column('is_admin', Boolean),
                Column('is_enabled', Boolean),
            )
            users.create()
        
        try:
            submissions = Table('submissions', metadata, autoload=True)
        except:
            submissions = Table('submissions', metadata,
                Column('id', Integer, primary_key=True),
                Column('parent_id', Integer),
                Column('name', String(255)),
                Column('data_path', String(255)),
                Column('description', String(255)),
                Column('form', String(255)),
                Column('created_at', DateTime),
                Column('is_complete', Boolean),
                Column('column_mappings', String(2048)),
            )
            
            # we will store the data in lots of appropriately-typed columns.
            # while the temptation might be to store each value in rows,
            # storing in columns and creating a view using the column mappings
            # will result in a much simpler and faster way to extract and
            # view the data
            for i in range(1, 50):
                # create 50 string and number fields to store the data into
                # the column_mapping field will name the columns appropriately
                submissions.append_column(Column("str%d" % i, String(255)))
                submissions.append_column(Column("int%d" % i, Integer))
                submissions.append_column(Column("flt%d" % i, Float))
            submissions.create()

        # updates to the table
        try:
            d = submissions.columns['data_path']
        except:
            db.execute('alter table submissions add column data_path varchar(255)')
    
    def save_node(self, node, parent_id, data_path):
        """This will save an XML node into the database, complete with
        column_mapping to map the data into the appropriately selected column.
        If it find a child node (such as the repeat login in the ODK form), then
        it processes it recursively
        """
        global col_num, col_name, col_actual, column_mappings, html_response
        html_response = ""
        col_num = 1
        col_name = 'unnamed'
        col_value = None
        col_actual = None
        column_mappings = ''

        data = {
            'name': node.nodeName,
            'data_path': data_path
        }

        if (parent_id <= 0):
            # root node
            try: data['form'] = node.attributes['id'].nodeValue
            except: pass
            try: data['description'] = node.attributes['name'].nodeValue
            except: pass
            data['is_complete'] = True
        else:
            # repeat child node
            data['parent_id'] = parent_id

        deferred_nodes = []
        
        def process(node):
            global col_num, col_name, col_actual, column_mappings, html_response
            for child in node.childNodes:
                col_name = child.nodeName
                col_value = None
                col_actual = "str%d" % col_num # TODO - numbers to go into intXX or fltXX field
                column_mappings += "%s as %s," % (col_actual, col_name)
                col_num += 1
                html_response += "<li> %s" % col_name
                if child.hasChildNodes() and child.firstChild.nodeType == child.TEXT_NODE:
                    col_value = child.firstChild.nodeValue
                    html_response += " = %s" % col_value
                elif child.hasChildNodes():
                    if flatten:
                        # the flatten option means all data gets written into the parent
                        process(child)
                    else:
                        # save the child nodes for processing later once we have the parent id
                        deferred_nodes.append(child)
                data[col_actual] = col_value

        process(node)
        data['column_mappings'] = column_mappings
        i = submissions.insert()
        result = i.execute(data)

        # insert child items
        for child in deferred_nodes:
            if not result.lastrowid: Exception("Database not supported")
            html_response += "<ul>%s</ul>" % self.save_node(child, result.lastrowid, data_path)
        
        return html_response        
    
    def submission(self, data_path, submission_dir):
        """Callback from odk server to save submitted data
        """
        global db
        
        html_response = ""
        dom = parse(data_path)
        
        root = dom.documentElement
        html_response += self.save_node(root, -1, submission_dir)
        html_response += "<li>Submit to db: %s - %s" % (data_path,db)
        return html_response

